#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""Module vhistory """

from string import Template
from tack.db import query, sql_int, sql_date, sql_string
from tack.lib import string_app, content_headers, status_codes, json_app
from tack.lib.jsontools import json_dumps
from tack.lib.dicttools import query_to_list
from tack_tmpo.api.schedule_tools import build_row_dict, sql_string
import time

# SQL type wrappers audited by Joshua Kugler, 2008-02-20

## SQL statements below

select_vhistory = Template("""
SELECT vh_id, dd1970_date, fuel, oil
FROM tmpo_vehicle_history
WHERE vehicle_id = $vehicle_id AND dd1970_date >='$start' 
AND dd1970_date <'$finish'
""")

get_one_on_date_and_key_sql = Template("""
SELECT *
FROM tmpo_vehicle_history
WHERE vehicle_id = $vehicle_id AND dd1970_date >= '$date_start' 
AND dd1970_date <= '$date_finish'
""")

get_actions_by_vh_id = Template("""
SELECT *
FROM tmpo_vehicle_history_action
WHERE vh_id = $vh_key
""")

get_destinations_by_vh_id = Template("""
SELECT *
FROM tmpo_vehicle_history_destination
WHERE vh_id = $vh_key
""")

update_vh_by_id = Template("""
UPDATE tmpo_vehicle_history
SET vehicle_id = $vehicle_id, dd1970_date = $dd1970_date, 
registration_number = $registration_number, administration_number = $administration_number, 
organization_name = $organization_name, departure_location = $departure_location, 
departure_time = $departure_time, fuel = $fuel, oil = $oil, passengers = $passengers, 
trip = $trip, tons_of_cargo = $tons_of_cargo, standby_status = $standby_status
WHERE vh_id = $vh_id
""")

insert_vh = Template("""
INSERT INTO tmpo_vehicle_history (vehicle_id, dd1970_date, registration_number, 
administration_number, organization_name, departure_location, departure_time, 
fuel, oil, passengers, trip, tons_of_cargo, standby_status)
VALUES ( $vehicle_id, $dd1970_date, $registration_number, $administration_number, 
$organization_name, $departure_location, $departure_time, $fuel, $oil, 
$passengers, $trip, $tons_of_cargo, $standby_status )
""")

select_tagno = Template("""
SELECT tagno
FROM tmpo_vehicle
WHERE vehicle_id = $key
""")

select_action = Template("""
SELECT vha_id, driver_id, checkin_miles, checkout_miles, checkin_time, 
checkout_time, poc_name
FROM tmpo_vehicle_history_action
WHERE vh_id = $id
""")

select_action_with_names = Template("""
SELECT tmpo_vehicle_history_action.vha_id, tmpo_vehicle_history_action.driver_id, 
tmpo_vehicle_history_action.checkin_miles, tmpo_vehicle_history_action.checkout_miles, 
tmpo_vehicle_history_action.checkin_time, tmpo_vehicle_history_action.checkout_time, 
tmpo_vehicle_history_action.poc_name, tmpo_driver.name
FROM tmpo_vehicle_history_action, tmpo_driver
WHERE tmpo_vehicle_history_action.vh_id = $id and 
tmpo_vehicle_history_action.driver_id=tmpo_driver.driver_id
""")

update_action_by_id = Template("""
UPDATE tmpo_vehicle_history_action
SET driver_id = $driver_id, checkin_miles=$checkin_miles, checkout_miles=$checkout_miles, 
checkin_time = $checkin_time, checkout_time=$checkout_time, poc_name=$poc_name
WHERE vha_id = $vha_id
""")

insert_vha = Template("""
INSERT INTO tmpo_vehicle_history_action (vh_id, driver_id, checkin_time, checkin_miles, 
checkout_time, checkout_miles, poc_name)
VALUES ( $vh_id, $driver_id, $checkin_time, $checkin_miles, $checkout_time, 
$checkout_miles, $poc_name )
""")

delete_vha_by_id = Template("""
DELETE FROM tmpo_vehicle_history_action
WHERE vha_id = $vha_id
""")

select_destination = Template("""
SELECT destination, arrival_time, departure_time, vhd_id, comments
FROM tmpo_vehicle_history_destination
WHERE vh_id = $id
""")

update_destination_by_id = Template("""
UPDATE tmpo_vehicle_history_destination
SET destination = $destination, arrival_time = $arrival_time, 
departure_time = $departure_time, comments = $comments
WHERE vhd_id = $vhd_id
""")

insert_vhd = Template("""
INSERT INTO tmpo_vehicle_history_destination (vh_id, destination, arrival_time, 
departure_time, comments)
VALUES ( $vh_id, $destination, $arrival_time, $departure_time, 
$comments )
""")

delete_vhd_by_id = Template("""
DELETE FROM tmpo_vehicle_history_destination
WHERE vhd_id = $vhd_id
""")


def ZIPLIST(labels,list):
    """Function ZIPLIST make a dictionary of two variables.  One is a list, the second
    is the labels for the lists.  
    ie; query_to_list([[1,2,3],[4,5,6],[7,8,9]],['one','two','three'])
    [{'three': 3, 'two': 2, 'one': 1}, {'three': 6, 'two': 5, 'one': 4}, {'three': 9, 'two': 8, 'one': 7}] """
    return [dict(zip(labels,i)) for i in list]

def get_vhistory(environ, start_response):
    """Function get_vhistory selects all rows from the tmpo_vehicle_history."""
    sql_statement = select_vhistory.substitute(
     vehicle_id = environ['dispatcher.groups']['vehicle_id'],
     start = environ['dispatcher.groups']['start'],
     finish = environ['dispatcher.groups']['finish']
    )
    output = {'history':[]}
    proto = {}
    history = output['history']
    rows = query(environ['connection'], sql_statement)
    for row in rows:
        tmp_history = proto.copy()
        tmp_history['dd1970_date'] = str(row[1])
        tmp_history['fuel'] = str(row[2])
        tmp_history['oil'] = str(row[3])
        oper_string = ""
        total_miles = 0
        sql_statement = select_action_with_names.substitute(
         id = row[0]
        )
        actions = query(environ['connection'], sql_statement)
        for action in actions:
            oper_string = oper_string + str(action[7])+","
            total_miles = total_miles + (action[2] - action[3])
        tmp_history['operators'] = oper_string
        tmp_history['miles'] = total_miles
        dest_string = ""
        sql_statement = select_destination.substitute(
        id = row[0]
        )
        destinations = query(environ['connection'], sql_statement)
        for destination in destinations:
            dest_string = dest_string + str(destination[0])+","
        tmp_history['destination'] = dest_string
        history.append(tmp_history)    
    return json_app(output)(environ, start_response)

def get_vhistory_keyed(environ, start_response):
    """Function get_vhistory_keyed selects rows in tmpo_vehicle_history 
    by id, start date, and end date."""
    sql = get_one_on_date_and_key_sql.substitute(
      vehicle_id = environ['dispatcher.groups']['vehicle_id'],
      date_start = environ['dispatcher.groups']['date'] + ' 00:00',
      date_finish = environ['dispatcher.groups']['date'] + ' 23:59'
    )
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    if rows:
        output = build_row_dict(cursor.description, rows[0])       
        actions_list = []
        sql = get_actions_by_vh_id.substitute(
         vh_key = rows[0].vh_id,
         date_start = environ['dispatcher.groups']['date'] + ' 00:00',
         date_finish = environ['dispatcher.groups']['date'] + ' 23:59'
        )
        cursor = environ['connection'].cursor()
        cursor.execute(sql)
        actions = cursor.fetchall()
        for action in actions:
            actions_list.append(build_row_dict(cursor.description, action))        
        output['actions'] = actions_list
        destinations_list = []
        sql = get_destinations_by_vh_id.substitute(
         vh_key = rows[0].vh_id
        )
        cursor = environ['connection'].cursor()
        cursor.execute(sql)
        destinations = cursor.fetchall()
        for destination in destinations:
            destinations_list.append(build_row_dict(cursor.description, destination))            
        output['destinations'] = destinations_list
    else:
        start_response(status_codes.not_found, content_headers.default_plain())
        return ['404 Not Found']
    return json_app(output)(environ, start_response)

def update_vh(environ, start_response):
    """Function update_vh updates a row in tmpo_vehicle_history with form variables and vh_id."""
    form = environ['form']
    sql_statement = update_vh_by_id.substitute(
      vh_id = environ['dispatcher.groups']['vh_id'],
      vehicle_id = sql_int(form['vehicle_id']),
      dd1970_date = sql_date(form['dd1970_date'],'%Y-%m-%d'),
      registration_number = sql_string(form['registration_number']),
      administration_number = sql_string(form['administration_number']),
      organization_name = sql_string(form['organization_name']),
      departure_location = sql_string(form['departure_location']),
      departure_time = sql_int(form['departure_time']),
      fuel = sql_int(form['fuel']),
      oil = sql_int(form['oil']),
      passengers = sql_int(form['passengers']),
      trip = sql_int(form['trip']),
      tons_of_cargo = sql_string(form['tons_of_cargo']),
      standby_status = sql_string(form['standby_status'])
    )
    rows = query(environ['connection'], sql_statement)
    output = 'Vehicle History Item Updated'
    return json_app(output)(environ, start_response)

def add_vh(environ, start_response):
    """Function add_vh inserts a row in tmpo_vehicle_history with form variables."""
    form = environ['form']
    sql_statement = insert_vh.substitute(
      vehicle_id = sql_int(form['vehicle_id']),
      dd1970_date = sql_date(form['dd1970_date'],'%Y-%m-%d'),
      registration_number = sql_string(form['registration_number']),
      administration_number = sql_string(form['administration_number']),
      organization_name = sql_string(form['organization_name']),
      departure_location = sql_string(form['departure_location']),
      departure_time = sql_int(form['departure_time']),
      fuel = sql_int(form['fuel']),
      oil = sql_int(form['oil']),
      passengers = sql_int(form['passengers']),
      trip = sql_int(form['trip']),
      tons_of_cargo = sql_string(form['tons_of_cargo']),
      standby_status = sql_string(form['standby_status'])
    ) 
    rows = query(environ['connection'], sql_statement)
    output = 'Vehicle History Item Added'
    return json_app(output)(environ, start_response)

def update_action(environ, start_response):
    """Function update_action updates a row in tmpo_vehicle_history_action with 
    form variables and action_id.""" 
    form = environ['form']
    sql_statement = update_action_by_id.substitute(
     vha_id = environ['dispatcher.groups']['vha_id'],
     checkin_miles = sql_int(form['checkin_miles']),
     checkout_miles = sql_int(form['checkout_miles']),
     poc_name = sql_string(str(form['poc_name'])),
     checkin_time = sql_int(form['checkin_time']),
     checkout_time = sql_int(form['checkout_time']),
     driver_id = sql_int(form['driver_id'])
    )
    rows = query(environ['connection'], sql_statement)
    output = 'Vehicle History Action Updated'
    return json_app(output)(environ, start_response)

def delete_vha(environ, start_response):
    """Function delete_vha deletes a row in tmpo_vehicle_history_action with 
    action_id."""
    sql_statement = delete_vha_by_id.substitute(
      vha_id = environ['dispatcher.groups']['vha_id']
    )
    rows = query(environ['connection'], sql_statement)
    output = 'Vehicle History Action Deleted'
    return json_app(output)(environ, start_response)

def add_vha(environ, start_response):
    """Function add_vha inserts a row in tmpo_vehicle_action with form variables.""" 
    form = environ['form']
    sql_statement = insert_vha.substitute(
      vh_id = sql_int(form['vh_id']),
      checkin_miles = sql_int(form['checkin_miles']),
      checkout_miles = sql_int(form['checkout_miles']),
      poc_name = sql_string(form['poc_name']),
      checkin_time = sql_int(form['checkin_time']),
      checkout_time = sql_int(form['checkout_time']),
      driver_id = sql_int(form['driver_id']),
    )
    rows = query(environ['connection'], sql_statement)
    output = 'Vehicle History Action Added'
    return json_app(output)(environ, start_response)

def update_destination(environ, start_response):
    """Function update_destination updates a row in tmpo_vehicle_history_destination with 
    form variables and vhd_id."""
    form = environ['form']  
    sql_statement = update_destination_by_id.substitute(
    vhd_id = environ['dispatcher.groups']['vhd_id'],
    destination = sql_string(form['destination']),
    arrival_time = sql_int(form['arrival_time']),
    departure_time = sql_int(form['departure_time']),
    comments = sql_string(form['comments']),
    )
    rows = query(environ['connection'], sql_statement)
    output = 'Vehicle History Destination Updated'
    return json_app(output)(environ, start_response)

def delete_vhd(environ, start_response):
    """Function delete_vhd deletes a row in tmpo_vehicle_history_destination with vhd_id.""" 
    sql_statement = delete_vhd_by_id.substitute(
    vhd_id = environ['dispatcher.groups']['vhd_id']
    )
    rows = query(environ['connection'], sql_statement)
    output = 'Vehicle History Destination Deleted'
    return json_app(output)(environ, start_response)

def add_vhd(environ, start_response):
    """Function add_vhd inserts a row in tmpo_vehicle_destination with form variables.""" 
    form = environ['form']
    sql_statement = insert_vhd.substitute(
    vh_id = sql_int(form['vh_id']),
    destination = sql_string(form['destination']),
    arrival_time = sql_int(form['arrival_time']),
    departure_time = sql_int(form['departure_time']),
    comments = sql_string(form['comments']),
    )
    rows = query(environ['connection'], sql_statement)
    output = 'Vehicle History Destination Added'
    return json_app(output)(environ, start_response)
